In [2]:
# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load in 

import altair as alt # For displaying graphs
import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)

# Input data files are available in the "../input/" directory.
# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory

import os
import requests
for dirname, _, filenames in os.walk('./icu-data/'):
    for filename in filenames:
        file = os.path.join(dirname, filename)
        print(file)

# Any results you write to the current directory are saved as output.

./icu-data/data-FPBfZ.csv

Data that specifies ICU Beds by State and County. It also factors in the Seniors (60+) in the County

In [3]:
demoGraphicAndICUByCounty = pd.read_csv(file)
print("Summary of ICU Beds per State and County with Residents (60+) per ICU Bed")
demoGraphicAndICUByCounty = demoGraphicAndICUByCounty.rename(columns = {"ICU Beds":"ICU_Beds","Total Population":"Total_Population","Population Aged 60+":"Population_Aged_60+","Percent of Population Aged 60+":"Percent_Population_Aged_60","Residents Aged 60+ Per Each ICU Bed":"Residents_Per_ICU_Bed"})
demoGraphicAndICUByCounty

Summary of ICU Beds per State and County with Residents (60+) per ICU Bed
Out[3]:
State County ICU_Beds Total_Population Population_Aged_60+ Percent_Population_Aged_60 Residents_Per_ICU_Bed
0 Alabama Autauga 6 55036 10523 19.1 1754.0
1 Alabama Baldwin 51 203360 53519 26.3 1049.0
2 Alabama Barbour 5 26201 6150 23.5 1230.0
3 Alabama Bibb 0 22580 4773 21.1 NaN
4 Alabama Blount 6 57667 13600 23.6 2267.0
... ... ... ... ... ... ... ...
3137 Wyoming Sweetwater 10 44527 7015 15.8 702.0
3138 Wyoming Teton 6 22923 4298 18.7 716.0
3139 Wyoming Uinta 6 20758 3554 17.1 592.0
3140 Wyoming Washakie 0 8253 2206 26.7 NaN
3141 Wyoming Weston 0 7117 2073 29.1 NaN

3142 rows × 7 columns

Data that specifies US County wise daily COVID19 count. This is a time series data account from 01 / Jan /2020)

In [4]:
jhuDailyDataSet=pd.read_csv('./us-counties/us-counties.csv')
print("Summary of JHU Daily Dataset across the US (Starting Jan 01 2020)")
jhuDailyDataSet

Summary of JHU Daily Dataset across the US (Starting Jan 01 2020)
Out[4]:
date county state fips cases deaths
0 2020-01-21 Snohomish Washington 53061.0 1 0
1 2020-01-22 Snohomish Washington 53061.0 1 0
2 2020-01-23 Snohomish Washington 53061.0 1 0
3 2020-01-24 Cook Illinois 17031.0 1 0
4 2020-01-24 Snohomish Washington 53061.0 1 0
... ... ... ... ... ... ...
23962 2020-03-31 Sheridan Wyoming 56033.0 10 0
23963 2020-03-31 Sublette Wyoming 56035.0 1 0
23964 2020-03-31 Sweetwater Wyoming 56037.0 2 0
23965 2020-03-31 Teton Wyoming 56039.0 23 0
23966 2020-03-31 Washakie Wyoming 56043.0 1 0

23967 rows × 6 columns

Most Vulnerable Counties having no ICU Beds

In [5]:
countWithNoICUBeds = demoGraphicAndICUByCounty.query("ICU_Beds==0")
countWithNoICUBeds
countiesWithICUBeds = demoGraphicAndICUByCounty.query("ICU_Beds>0")
In [6]:
print("Most Vulnerable Counties having No ICU Beds")
alt.Chart(countWithNoICUBeds).mark_circle(size=50).encode(
    x='Total_Population',
    y='Population_Aged_60+',
    color='State',
    tooltip=['State', 'County','Percent_Population_Aged_60','Population_Aged_60+','Total_Population']
).interactive()

Most Vulnerable Counties having No ICU Beds
Out[6]:
In [7]:
# Going to concatenate Vulnerable Counties with No ICU Beds and Day wise COVID-19 Cases and Deaths
# Have to aggregate jhuDataSet by Total Cases and Total Deaths

jhuDailyDataSet = jhuDailyDataSet.rename(columns={'state':'State','county':'County'})
jhuAggregateDataSet = jhuDailyDataSet.groupby(['State','County'])
jhuAggregateDataSetAggregate = jhuAggregateDataSet.agg(np.sum)
jhuAggregateDataSetAggregate
mergedDataSet = pd.merge(jhuAggregateDataSetAggregate,countiesWithICUBeds,left_on='State',right_on='State')
alt.data_transformers.disable_max_rows()
print("Vulnerable State and Counties with maximum residents per ICU and higher Cases of COVID19")
alt.Chart(mergedDataSet).mark_circle(size=50).encode(
    x='cases',
    y='Residents_Per_ICU_Bed',
    color='State',
    tooltip=['State', 'County','Percent_Population_Aged_60','Population_Aged_60+','Total_Population','cases','deaths','Residents_Per_ICU_Bed']
).interactive()

Vulnerable State and Counties with maximum residents per ICU and higher Cases of COVID19
Out[7]:
In [ ]: